﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Drawing;


// w tym pliku znajdą sie wszystkie
// funkcje zwiazane z obsługa słowników

// sekcja z opcjami nie jest przetestowana w związku z moimi problemami
// z sql serverem... mam nadzieje ze nic nie spierdoliłem :P
// ... ale sie kompiluje poki co

namespace Salon
{
    class DictService
    {
        public DictService() { }

        public static readonly int ERROR = -1;
        public static readonly int OK = 0;
        public static readonly int ACTIVATED = 1;

        //
        //  Kolory
        //

        public IList<DictColorItem> getColors()
        {
            IList<DictColorItem> list = new List<DictColorItem>();
            String query = "SELECT * FROM Colors WHERE active=1 ";
            SqlDataReader reader = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    DictColorItem item = new DictColorItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.Color = Color.FromArgb(reader.GetInt32(reader.GetOrdinal("argb")));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public IList<DictColorItem> getColors(IList<IFilter> filterList)
        {
            IList<DictColorItem> list = new List<DictColorItem>();
            String query = "SELECT * FROM Colors WHERE id > 0 AND active=1 ";

            if (filterList != null) query += Global.getConditionString(filterList);

            SqlDataReader reader = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    DictColorItem item = new DictColorItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.Color = Color.FromArgb(reader.GetInt32(reader.GetOrdinal("argb")));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public DictColorItem getColorById(int id)
        {
            DictColorItem item = new DictColorItem();

            String query = "SELECT * FROM Colors WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", id);
            SqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
                return null;
            reader.Read();

            item.Id = reader.GetInt32(reader.GetOrdinal("id"));
            item.Color = Color.FromArgb(reader.GetInt32(reader.GetOrdinal("argb")));
            item.DictValue = reader.GetString(reader.GetOrdinal("value"));
            item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

            reader.Close();
            return item;
        }
        public DictColorItem getColorByName(string name)
        {
            DictColorItem item = new DictColorItem();

            String query = "SELECT * FROM Colors WHERE value=@value";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@value", name);
            SqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
                return null;
            reader.Read();

            item.Id = reader.GetInt32(reader.GetOrdinal("id"));
            item.Color = Color.FromArgb(reader.GetInt32(reader.GetOrdinal("argb")));
            item.DictValue = reader.GetString(reader.GetOrdinal("value"));
            item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

            reader.Close();
            return item;
        }
        public void deleteColor(DictColorItem item)
        {
            String query = "UPDATE Colors SET active=0 WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", item.Id);
            cmd.ExecuteNonQuery();
        }
        public int addColor(DictColorItem item)
        {
            DictColorItem old = getColorByName(item.DictValue);
            String query;
            SqlCommand cmd;
            if (old != null)
            {
                if (old.Active == false)
                {
                    query = "UPDATE Colors SET active=1 WHERE id=@id";
                    cmd = SqlService.getInstance().getCommand(query);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@id", old.Id);
                    cmd.ExecuteNonQuery();

                    return ACTIVATED;
                }
                return ERROR;
            }

            query = "INSERT INTO Colors(value,argb,active) VALUES (@value,@argb,1)";
            cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@value", item.DictValue);
            cmd.Parameters.AddWithValue("@argb", item.Color.ToArgb());
            cmd.ExecuteNonQuery();

            return OK;
        }
        public int updateColor(DictColorItem item)
        {
            if (item == null) return ERROR;
            DictColorItem old_name=null, old_id=null;


            // (1) Jeśli podano ID to musi takie istnieć!

            // Pobranie koloru o zadanym id jeśli istnieje
            if (item.Id > 0)
            {
                old_id = getColorById(item.Id);

                // jeśli podane ID nie istnieje to błędny update (nie można zmienić ID)
                if (old_id == null) return ERROR;
            }


            // (2)  Jeśli nie podano nazwy koloru to musi zostać podane prawidłowe ID
            // [NP]  wtedy nazwa ma zostać nie zmieniona i zmienia się jedynie kolor.

            // Jeśli nie podano nazwy koloru
            if (item.DictValue == "")
            {
                // sprawdzenie czy isteje ID
                if (old_id == null) return ERROR; // nie ma takiego koloru

                // UPDATE koloru o podanym ID (nie podano nazwy - zostaje stara)


                String query;
                SqlCommand cmd;
                query = "UPDATE Colors SET active=1, argb=@argb WHERE id=@id";
                cmd = SqlService.getInstance().getCommand(query);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@id", old_id.Id); // stare id
                cmd.Parameters.AddWithValue("@argb", item.Color.ToArgb()); // nowy kolor
                cmd.ExecuteNonQuery();


                return OK;
            }
            // Pobranie koloru o podanej nazwie
            else 
            {
                old_name = getColorByName(item.DictValue);
            }


            // (3)  Jeśli nie podano ID to znajdujemy kolor o podanej nawie
            // [PN]

            // Jeśli nie ma koloru o podanym ID
            if (old_id == null)
            {
                // sprawdzenie czy istnieje podana NAZWA
                if (old_name == null) return ERROR; // nie ma takiego koloru

                // UPDATE koloru o podanej nazwie


                String query;
                SqlCommand cmd;
                query = "UPDATE Colors SET active=1, argb=@argb WHERE id=@id";
                cmd = SqlService.getInstance().getCommand(query);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@id", old_name.Id); // stare id
                cmd.Parameters.AddWithValue("@argb", item.Color.ToArgb()); // nowy kolor
                cmd.ExecuteNonQuery();


                return OK;
            }
            
            
            // (4)  Jeśli nie ma koloru o podanej nazwie (ale podano jakąś nową nazwę)
            // [BP]  to aktualizacja nazwy oraz koloru o podanym ID

            if (old_name == null)
            {
                // sprawdzenie czy istnieje podane ID
                if (old_id == null) return ERROR;

                // UPDATE koloru o podanym ID


                String query;
                SqlCommand cmd;
                query = "UPDATE Colors SET active=1, value=@value, argb=@argb WHERE id=@id";
                cmd = SqlService.getInstance().getCommand(query);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@id", old_id.Id); // stare id
                cmd.Parameters.AddWithValue("@value", item.DictValue); // nowa nazwa
                cmd.Parameters.AddWithValue("@argb", item.Color.ToArgb()); // nowy kolor
                cmd.ExecuteNonQuery();


                return OK;
            }


            // (5)  Jeśli istnieje podane ID oraz NAZWA (i jest to ten sam kolor)
            // [PP]

            if (old_id.Id == old_name.Id)
            {
                // UPDATE koloru  dla podanego prze NAZWĘ i ID


                String query;
                SqlCommand cmd;
                query = "UPDATE Colors SET active=1, argb=@argb WHERE id=@id AND value LIKE @value ";
                cmd = SqlService.getInstance().getCommand(query);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@id", old_id.Id); // stare id
                cmd.Parameters.AddWithValue("@value", old_name.DictValue); // stara nazwa
                cmd.Parameters.AddWithValue("@argb", item.Color.ToArgb()); // nowy kolor
                cmd.ExecuteNonQuery();


                return OK;
            }

            return ERROR; // We wszystkich pozostałych przypadkach błąd

        }


        //
        //  Marki
        //

        public IList<DictionaryItem> getMarks()
        {
            IList<DictionaryItem> list = new List<DictionaryItem>();
            String query = "SELECT * FROM Mark WHERE active=1";
            SqlDataReader reader = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    DictionaryItem item = new DictionaryItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public IList<DictionaryItem> getMarks(IList<IFilter> filterList)
        {
            IList<DictionaryItem> list = new List<DictionaryItem>();
            String query = "SELECT * FROM Mark WHERE id > 0 AND active=1  ";

            if (filterList != null) query += Global.getConditionString(filterList);

            SqlDataReader reader = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    DictionaryItem item = new DictionaryItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public DictionaryItem getMarkById(int id)
        {
            DictionaryItem item = new DictionaryItem();

            String query = "SELECT * FROM Mark WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", id);
            SqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
                return null;
            reader.Read();

            item.Id = reader.GetInt32(reader.GetOrdinal("id"));
            item.DictValue = reader.GetString(reader.GetOrdinal("value"));
            item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

            reader.Close();
            return item;
        }
        public DictionaryItem getMarkByName(string name)
        {
            DictionaryItem item = new DictionaryItem();

            String query = "SELECT * FROM Mark WHERE value=@value";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@value", name);
            SqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
                return null;
            reader.Read();

            item.Id = reader.GetInt32(reader.GetOrdinal("id"));
            item.DictValue = reader.GetString(reader.GetOrdinal("value"));
            item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

            reader.Close();
            return item;
        }
        public void deleteMark(DictionaryItem item)
        {
            String query = "UPDATE Mark SET active=0 WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", item.Id);
            cmd.ExecuteNonQuery();
        }
        public int addMark(DictionaryItem item)
        {
            DictionaryItem old = getMarkByName(item.DictValue);
            String query;
            SqlCommand cmd;
            if (old != null)
            {
                if (old.Active == false)
                {
                    query = "UPDATE Mark SET active=1 WHERE id=@id";
                    cmd = SqlService.getInstance().getCommand(query);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@id", old.Id);
                    cmd.ExecuteNonQuery();

                    return ACTIVATED;
                }
                return ERROR;
            }

            query = "INSERT INTO Mark(value,active) VALUES (@value,1)";
            cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@value", item.DictValue);
            cmd.ExecuteNonQuery();

            return OK;
        }
        public int updateMark(DictionaryItem item)
        {
            DictionaryItem old = getMarkById(item.Id);
            String query;
            SqlCommand cmd;
            if (old != null) // jeśli dane id marki jest w bazie
            {
                query = "UPDATE Mark SET active=1, value=@value WHERE id=@id";
                cmd = SqlService.getInstance().getCommand(query);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@id", old.Id); // stare id
                cmd.Parameters.AddWithValue("@value", item.DictValue); // nowa nazwa
                cmd.ExecuteNonQuery();

                return OK;
            }
            return ERROR;
        }

        //
        //  Modele
        //

        public IList<DictModelItem> getModels()
        {
            IList<DictModelItem> list = new List<DictModelItem>();
            String query = "SELECT * FROM Models WHERE active=1 ";
            SqlDataReader reader = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    DictModelItem item = new DictModelItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.MarkId = reader.GetInt32(reader.GetOrdinal("mark"));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public IList<DictModelItem> getModels(IList<IFilter> filterList)
        {
            IList<DictModelItem> list = new List<DictModelItem>();
            String query = "SELECT * FROM Models WHERE id > 0 AND active=1 ";

            if (filterList != null) query += Global.getConditionString(filterList);

            SqlDataReader reader = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    DictModelItem item = new DictModelItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.MarkId = reader.GetInt32(reader.GetOrdinal("mark"));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public DictModelItem getModelById(int id)
        {
            DictModelItem item = new DictModelItem();

            String query = "SELECT * FROM Models WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", id);
            SqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
                return null;
            reader.Read();

            item.Id = reader.GetInt32(reader.GetOrdinal("id"));
            item.DictValue = reader.GetString(reader.GetOrdinal("value"));
            item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));
            item.MarkId = reader.GetInt32(reader.GetOrdinal("mark"));

            reader.Close();
            return item;
        }
        public DictModelItem getModelByName(string name)
        {
            DictModelItem item = new DictModelItem();

            String query = "SELECT * FROM Models WHERE value=@value";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@value", name);
            SqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
                return null;
            reader.Read();

            item.Id = reader.GetInt32(reader.GetOrdinal("id"));
            item.DictValue = reader.GetString(reader.GetOrdinal("value"));
            item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));
            item.MarkId = reader.GetInt32(reader.GetOrdinal("mark"));

            reader.Close();
            return item;
        }
        public void deleteModel(DictModelItem item)
        {
            String query = "UPDATE Models SET active=0 WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", item.Id);
            cmd.ExecuteNonQuery();
        }
        public int addModel(DictModelItem item)
        {
            // sprawdzenie czy istnieje już model o takiej nazwie
            DictModelItem old = getModelByName(item.DictValue); 
            String query;
            SqlCommand cmd;
            if (old != null)
            {
                if (old.Active == false) // tylko aktywacja bez edycji
                {
                    query = "UPDATE Models SET active=1 WHERE id=@id";
                    cmd = SqlService.getInstance().getCommand(query);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@id", old.Id);
                    cmd.ExecuteNonQuery();

                    return ACTIVATED;
                }
                return ERROR;
            }

            query = "INSERT INTO Models(value,active,mark) VALUES (@value,1,@mark)";
            cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@value", item.DictValue);
            cmd.Parameters.AddWithValue("@mark", item.MarkId);
            cmd.ExecuteNonQuery();

            return OK;
        }
        public int updateModel(DictModelItem item)
        {
            DictModelItem old = getModelById(item.Id);
            String query;
            SqlCommand cmd;
            if (old != null) // jeśli ten kolor jest w bazie (id)
            {
                query = "UPDATE Models SET active=1, value=@value, mark=@mark WHERE id=@id";
                cmd = SqlService.getInstance().getCommand(query);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@id", old.Id); // stare id
                //cmd.Parameters.AddWithValue("@value", old.DictValue); // stara nazwa
                //cmd.Parameters.AddWithValue("@mark", old.DictValue); // stara marka
                cmd.Parameters.AddWithValue("@value", item.DictValue); // nowa nazwa
                cmd.Parameters.AddWithValue("@mark", item.DictValue); // nowa marka
                cmd.ExecuteNonQuery();

                return OK;
            }
            return ERROR;
        }

        //
        //  Opcje
        //


        public IList<DictOptionsItem> getOptions()
        {
            IList<DictOptionsItem> list = new List<DictOptionsItem>();
            String query = "SELECT * FROM Options WHERE active=1 "; // czy ta tabela tak sie nazywa?
            SqlDataReader reader = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    DictOptionsItem item = new DictOptionsItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public IList<DictOptionsItem> getOptions(IList<IFilter> filterList)
        {
            IList<DictOptionsItem> list = new List<DictOptionsItem>();
            String query = "SELECT * FROM Options WHERE id > 0 AND active=1 ";

            if (filterList != null) query += Global.getConditionString(filterList);

            SqlDataReader reader = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    DictOptionsItem item = new DictOptionsItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public DictOptionsItem getOptionById(int id)
        {
            DictOptionsItem item = new DictOptionsItem();

            String query = "SELECT * FROM Options WHERE id=@id";    //// nazwa tabeli poprawna???
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", id);
            SqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
                return null;
            reader.Read();

            item.Id = reader.GetInt32(reader.GetOrdinal("id"));
            item.DictValue = reader.GetString(reader.GetOrdinal("value"));
            item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

            reader.Close();
            return item;
        }
        public DictOptionsItem getOptionByName(string name)
        {
            DictOptionsItem item = new DictOptionsItem();

            String query = "SELECT * FROM Options WHERE value=@value"; 
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@value", name);
            SqlDataReader reader = cmd.ExecuteReader();
            if (!reader.HasRows)
                return null;
            reader.Read();

            item.Id = reader.GetInt32(reader.GetOrdinal("id"));
            item.DictValue = reader.GetString(reader.GetOrdinal("value"));
            item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

            reader.Close();
            return item;
        }
        public void deleteOption(DictOptionsItem item)
        {
            String query = "UPDATE Options SET active=0 WHERE id=@id"; /// czy to je ok?
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", item.Id);
            cmd.ExecuteNonQuery();
        }
        public int addOption(DictOptionsItem item)
        {
            // sprawdzenie czy istnieje już opcja o takiej nazwie
            DictOptionsItem old = getOptionByName(item.DictValue);
            String query;
            SqlCommand cmd;
            if (old != null)
            {
                if (old.Active == false) // tylko aktywacja bez edycji
                {
                    query = "UPDATE Options SET active=1 WHERE id=@id"; 
                    cmd = SqlService.getInstance().getCommand(query);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@id", old.Id);
                    cmd.ExecuteNonQuery();

                    return ACTIVATED;
                }
                return ERROR;
            }

            query = "INSERT INTO Options(value,active) VALUES (@value,1)"; // option czy options?
            cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@value", item.DictValue);
            cmd.ExecuteNonQuery();

            return OK;
        }
        public int updateOption(DictOptionsItem item)
        {                         
            DictOptionsItem old = getOptionById(item.Id);
            String query;
            SqlCommand cmd;
            if (old != null) // jeśli ta opcja jest w bazie (id)
            {
                query = "UPDATE Options SET active=1, value=@value WHERE id=@id"; 
                cmd = SqlService.getInstance().getCommand(query);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@id", old.Id); // stare id
                cmd.Parameters.AddWithValue("@value", item.DictValue); // nowa nazwa 
                cmd.ExecuteNonQuery();

                return OK;
            }
            return ERROR;
        }

        // Związki opcji z modelem i konkretnum samochodem

        public IList<ModelOptionItem> getModelOptions(int ModelId)
        {
            IList<ModelOptionItem> list = new List<ModelOptionItem>();
            //String query = "SELECT * FROM Options,  WHERE id > 0 ";
            String query = "SELECT * FROM Options o JOIN Model_option mo ON o.id=mo.option_id WHERE type_id = @model_id";

            //if (filterList != null) query += Global.getConditionString(filterList);

            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@model_id", ModelId);
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    ModelOptionItem item = new ModelOptionItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));
                    item.Status = (ModelOptionItem.EnumStatus) reader.GetInt32(reader.GetOrdinal("status"));
                    item.Price = (decimal)reader.GetFloat(reader.GetOrdinal("price"));
                    item.Cost = (decimal)reader.GetFloat(reader.GetOrdinal("cost"));
                    item.Discount = (decimal)reader.GetFloat(reader.GetOrdinal("discount"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public IList<CarOptionItem> getCarOptions(int CarId)
        {
            IList<CarOptionItem> list = new List<CarOptionItem>();
            //String query = "SELECT * FROM Options,  WHERE id > 0 ";
            String query = "SELECT * FROM Options o JOIN Car_options co ON o.id=co.opt_id WHERE car_id = @car_id";

            //if (filterList != null) query += Global.getConditionString(filterList);

            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@car_id", CarId);
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    CarOptionItem item = new CarOptionItem();
                    item.Id = reader.GetInt32(reader.GetOrdinal("id"));
                    item.DictValue = reader.GetString(reader.GetOrdinal("value"));
                    item.Active = (bool)reader.GetSqlBoolean(reader.GetOrdinal("active"));

                    item.State = (CarOptionItem.EnumState) reader.GetInt32(reader.GetOrdinal("state"));
                    item.Price = reader.GetFloat(reader.GetOrdinal("price"));
                    item.Cost = reader.GetFloat(reader.GetOrdinal("cost"));
                    item.Discount = reader.GetFloat(reader.GetOrdinal("discount"));
                    item.MountDate = reader.GetDateTime(reader.GetOrdinal("mount_date"));
                    //item.ServicemanId = reader.GetInt32(reader.GetOrdinal("serviceman_id"));

                    list.Add(item);
                }
            }

            reader.Close();

            return list;
        }
        public void addCarOptions(int CarId, IList<CarOptionItem> list)
        {
            String insert_query = "INSERT INTO Car_options(car_id,opt_id,state,serviceman_id,mount_date,cost,price,discount) VALUES(@car_id,@opt_id,@state, null ,@mount_date,@cost,@price,@discount) ";
            //String update_query = "UPDATE Car_options SET state=@state, serviceman_id=@serviceman_id, mount_date=@mount_date,cost=@cost,price=@price,discount=@discount,order_id=@order_id WHERE car_id = @car_id AND opt_id = @opt_id";

            SqlCommand cmd;// = SqlService.getInstance().getCommand(insert_query);

            foreach (CarOptionItem item in list)
            {
                cmd = SqlService.getInstance().getCommand(insert_query);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@car_id", CarId);
                cmd.Parameters.AddWithValue("@opt_id", item.Id);
                cmd.Parameters.AddWithValue("@state", item.State);
                //cmd.Parameters.AddWithValue("@serviceman_id", item.ServicemanId);
                cmd.Parameters.AddWithValue("@mount_date", item.MountDate);
                cmd.Parameters.AddWithValue("@price", item.Price);
                cmd.Parameters.AddWithValue("@cost", item.Cost);
                cmd.Parameters.AddWithValue("@discount", item.Discount);
                //cmd.Parameters.AddWithValue("@order_id", 25);

                cmd.ExecuteNonQuery();
            }

        }
    }
}
